package com.dongwu.manager.dao;

import java.util.List;

import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import com.dongwu.common.dao.BaseDao;
import com.dongwu.manager.entity.DeviceAdsRecord;

@Repository
public interface DeviceAdsRecordDao extends BaseDao<DeviceAdsRecord, String> {

	public List<DeviceAdsRecord> findByDeviceidAndStatus(String deviceId, Integer status);
	
	@Query(value="select COUNT("
			+ "CASE WHEN dpr.end_time is NULL  and dpr.start_time is NULL THEN '1' "
			+ "WHEN dpr.end_time is NOT NULL  and dpr.start_time is NULL and dpr.end_time >= now()  THEN '2' "
			+ "WHEN dpr.end_time is NULL  and dpr.start_time is NOT NULL and dpr.start_time <= now() THEN '3' "
			+ "WHEN dpr.end_time is NOT NULL  and dpr.start_time is NOT NULL and dpr.start_time <= now() and dpr.end_time >= now() THEN '4' "
			+ "ELSE NULL "
			+ "END) "
			+ "AS dprcount "
			+ "FROM device_ads_record dpr "
			+ "where userid = ?1", nativeQuery=true)//原生sql语句
	public Long countByUserid(String userid);
}
